package kr.hkit.score_pjt.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.framework.dao.CloseDaoUtil;
import kr.hkit.score_pjt.DTO.StdInfo;

public class StdInfoDao {
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	public StdInfoDao() {
	}
	
	public StdInfoDao(Connection con) {
		this.con = con;
	}
	
	public void setCon(Connection con) {
		this.con = con;
	}
	
	public void insertProduct(StdInfo dto) throws SQLException{
		String sql = "insert into stdinfo values(?,?,?,?,?)";
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, dto.getStdNum());
		pstmt.setString(2, dto.getStdName());
		pstmt.setInt(3, dto.getKorScore());
		pstmt.setInt(4, dto.getEngScore());
		pstmt.setInt(5, dto.getMathScore());
		
		pstmt.executeUpdate();
		
		CloseDaoUtil.close(pstmt);
	}
	
	public ArrayList<StdInfo> selectAllStdInfoLists() throws Exception{
		String sql ="select grade, rank, stdnum, stdname, kor, eng, math, total, avg from view_stdinfo";
		ArrayList<StdInfo> lists = new ArrayList<>();
		StdInfo std = null;
		
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		
		while(rs.next()){
			std = new StdInfo();
			std.setStdGrade(rs.getInt(1)).setStdRank(rs.getInt(2));
			std.setStdNum(rs.getInt(3)).setStdName(rs.getString(4));
			std.setKorScore(rs.getInt(5)).setEngScore(rs.getInt(6));
			std.setMathScore(rs.getInt(7)).setTotal(rs.getInt(8));
			std.setAvg(rs.getInt(9));
			lists.add(std);
		}
		if(lists.size() == 0){
			throw new Exception("학생 정보가 없습니다");
		}
		return lists;
	}
	
	public void deleteAllStdInfo() throws SQLException{
		String sql = "delete from stdinfo";
		
		pstmt = con.prepareStatement(sql);
		pstmt.executeUpdate();
		CloseDaoUtil.close(pstmt);
	}
	
	public String selectStdnameByStdNum(int stdnum) throws Exception{
		String sql = "select stdname from stdcode where stdnum=?";
		String stdname = null;
		
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, stdnum);
		rs = pstmt.executeQuery();
		
		if(rs.next()){
			stdname = rs.getString(1);
		} else {
			throw new Exception("해당 학번이 존재하지 않음.");
		}
		
		return stdname;
	}
}
